﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Collections;
using System.Text;
using Fcl.DataWork;
using Fcl.WSOP;
using System.IO;
using System.Xml;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Web;

namespace LicPdaService
{
    /// <summary>
    /// App 的摘要说明
    /// </summary>
    public class App
    {
        public static string sasn = "";
        public static string swhseid = "";
        public static string suserid = "";
        public static sqldbop ws;
        private static string _whseid;
        private static string _userId;
        private static string _userName;
        private static string _customer_code;
        public static string DBconWMS = string.Empty;
        public static string ErrMessage = string.Empty;
        /// <summary>
        /// 客户代码
        /// </summary>
        public string customer_code
        {
            get
            { return _customer_code; }
            set
            { _customer_code = value; }
        }
        /// <summary>
        /// 仓库代码
        /// </summary>
        public string whseid
        {
            get
            { return _whseid; }
            set
            { _whseid = value; }
        }
        /// <summary>
        /// 用户ID
        /// </summary>
        public static string userId
        {
            get { return _userId; }
            set { _userId = value; }
        }
        /// <summary>
        /// 用户名称
        /// </summary>
        public static string userName
        {
            get { return _userName; }
            set { _userName = value; }
        }

        public static void Connection()
        {
            //App.ws = new Fcl.WSOP.sqldbop();
            //App.ws.Init(System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        }

        public static void DBconSet()
        {
            try
            {
                DBconWMS = System.Configuration.ConfigurationManager.ConnectionStrings["adpwmsConnectionString"].ConnectionString;
            }
            catch (Exception ex)
            {
                App.Log("App.cs-DBconSet", ex.Message.ToString());
            }
        }


        //将datatable转为xml
        public static string DataTable2Xml(DataTable ldt)
        {
            if (ldt == null) return string.Empty;
            StringWriter writer = new StringWriter();
            ldt.WriteXml(writer);
            string xmlstr = writer.ToString();
            xmlstr = "<?xml version='1.0' encoding='UTF-8' ?> " + xmlstr;
            writer.Close();
            return xmlstr;
        }

        //将xml转为Datable
        public static DataTable Xml2DataTable(string lxmlStr)
        {

            XmlDocument doc = new XmlDocument();
            doc.LoadXml(lxmlStr);
            XmlNodeReader reader = new XmlNodeReader(doc);
            DataSet ds = new DataSet();
            ds.ReadXml(reader);
            reader.Close();
            return ds.Tables[0];
        }

        //将xml转为Datable （针对xml,可以转化成两张表情况）
        public static DataTable Xml3DataTable(string lxmlStr)
        {

            XmlDocument doc = new XmlDocument();
            doc.LoadXml(lxmlStr);
            XmlNodeReader reader = new XmlNodeReader(doc);
            DataSet ds = new DataSet();
            ds.ReadXml(reader);
            reader.Close();
            return ds.Tables[1];//取第二张表
        }

        //上架、打印时需要组成新的表
        public static DataTable GetCreateDataTable()
        {
            DataTable ldt_new = new DataTable();
            ldt_new.Columns.Add("asn");
            ldt_new.Columns.Add("sku");
            ldt_new.Columns.Add("lottable02");
            ldt_new.Columns.Add("lottable01");
            ldt_new.Columns.Add("shipkey");
            ldt_new.Columns.Add("master_label");
            ldt_new.Columns.Add("pre_qty");
            ldt_new.Columns.Add("qty");
            ldt_new.Columns.Add("lpn");
            ldt_new.Columns.Add("storage");
            ldt_new.Columns.Add("line_no");
            ldt_new.Columns.Add("msg");
            ldt_new.Columns.Add("userid");
            ldt_new.Columns.Add("whseid");
            ldt_new.Columns.Add("check");
            ldt_new.Columns.Add("customer_code");
            ldt_new.Columns.Add("test");
            return ldt_new;
        }

        public static DataTable GetNewDataTabel(DataTable ldt)
        {
            DataTable ldt_new = GetCreateDataTable();
            for (int i = 0; i < ldt.Rows.Count; i++)
            {

                string[] ls;
                ls = ldt.Rows[i]["master_label"].ToString().Split('|');
                if (ls.Length == 5 || ls.Length == 4)
                {
                    DataRow ldr = ldt_new.NewRow();
                    ldr["asn"] = ldt.Rows[0]["asn"].ToString();
                    ldr["sku"] = ls[0].ToString();
                    ldr["lottable02"] = ls[1].ToString();
                    ldr["lottable01"] = ls[2].ToString();
                    if (ls.Length == 5)
                    {
                        ldr["shipkey"] = ls[4].ToString();
                    }

                    ldr["qty"] = Convert.ToSingle(ls[3].ToString());
                    ldr["master_label"] = ldt.Rows[i]["master_label"].ToString();
                    ldr["userid"] = ldt.Rows[0]["userid"].ToString();
                    ldr["whseid"] = ldt.Rows[0]["whseid"].ToString();
                    if (ldt.Columns.Contains("storage"))
                    {
                        ldr["storage"] = ldt.Rows[i]["storage"].ToString();

                    }
                    else
                    {
                        ldr["storage"] = "UNKNOW";
                    }


                    ldr["msg"] = "";
                    if (ldt.Columns.Contains("check"))
                    {
                        ldr["check"] = ldt.Rows[i]["check"].ToString();
                    }
                    if (ldt.Columns.Contains("lpn"))
                    {
                        ldr["lpn"] = ldt.Rows[i]["lpn"].ToString();
                    }

                    ldt_new.Rows.Add(ldr);
                }
            }

            return ldt_new;
        }

        public static void Log(string lstype, string lsmsg)
        {
            string lspath = System.Web.HttpContext.Current.Request.MapPath("/");
            lspath += "log";
            if (System.IO.Directory.Exists(lspath))
            {
                System.IO.Directory.CreateDirectory(lspath);
            }
            string lsfile = lspath + "\\" + System.DateTime.Now.ToString().Replace("/", "").Replace(":", "") + ".txt";
            System.IO.File.AppendAllText(lsfile, lstype + ":" + lsmsg);

        }

        //public static bool ExecuteSQLScriptsLists(List<string> sqlLists)
        //{
        //    string tranid = string.Empty;
        //    string sqltemp = string.Empty;
        //    List<Fcl.WSOP.SqlBuilder> sqlBuilderLists = new List<Fcl.WSOP.SqlBuilder>();
        //    try
        //    {
        //        for (int i = 0; i <= sqlLists.Count - 1; i++)
        //        {
        //            sqltemp = sqlLists[i].ToString();
        //            if (sqltemp.Length > 0)
        //            {
        //                sqlBuilderLists.Add(new Fcl.WSOP.SqlBuilder(sqltemp));
        //            }
        //        }
        //        ws.ExecNonQuery(sqlBuilderLists.ToArray(), string.Empty);
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        Log("APP.ExecuteSQLScriptsLists", ex.Message.ToString());
        //        return false;
        //    }
        //}

        /// <summary>
        /// 获取订单明细行号
        /// </summary>
        /// <param name="tabName">表名</param>
        /// <param name="whseid">仓库代码</param>
        /// <param name="orderno">订单号</param>
        /// <param name="field">字段名</param>
        /// <returns></returns>
        public static string getOrderRowNo(string tabName, string field, string whseid, string orderno)
        {
            string result = "";
            int count = 1;
            string strSql = "select max(line_no) line_no from " + tabName + " where whseid='" + whseid + "' and " + field + "='" + orderno + "'";
            DataTable dt = GetDataTable(DBconWMS, strSql, "orderkey");
            if (dt.Rows[0]["line_no"].ToString() != "")
                count = Int32.Parse(dt.Rows[0]["line_no"].ToString()) + 1;
            result = count.ToString().PadLeft(5, '0');
            return result;
        }

        //判定是否为纯数字
        public static bool IsNumber(string str)
        {
            if (str == null || str.Length == 0)    //验证这个参数是否为空  
                return false;                           //是，就返回False  
            ASCIIEncoding ascii = new ASCIIEncoding();//new ASCIIEncoding 的实例  
            byte[] bytestr = ascii.GetBytes(str);         //把string类型的参数保存到数组里  

            foreach (byte c in bytestr)                   //遍历这个数组里的内容  
            {
                if (c < 48 || c > 57)                          //判断是否为数字  
                {
                    return false;                              //不是，就返回False  
                }
            }
            return true;                                        //是，就返回True  
        }

        public static string GetPutAway(DataTable dt)
        {
            try
            {
                App.DBconSet(); //数据库连接设定
                string loc = "UNKNOW";
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string test = dt.Rows[i]["test"].ToString();

                    if (test == "1")
                    {
                        return loc = "QC";
                    }

                    #region 根据料号、项目号设置的存储区、查找没有被占用库位。
                    string strSql = "select location.loc from  dbo.SkuLocation ";
                    strSql += " left join location  on location.zone_code=SkuLocation.zone_code and  location.whseid=SkuLocation.whseid ";
                    strSql += " left join sku on sku.whseid=SkuLocation.whseid and sku.customer_code=SkuLocation.customer_code and  sku.sku=SkuLocation.sku";
                    strSql += " where SkuLocation.whseid='" + dt.Rows[0]["whseid"].ToString() + "' and  SkuLocation.customer_code='" + dt.Rows[0]["customer_code"].ToString() + "' and SkuLocation.sku='" + dt.Rows[0]["sku"].ToString() + "'";
                    strSql += " and  location.loc not in  (select loc from inventory where  inventory.whseid='" + dt.Rows[0]["whseid"].ToString() + "' and qty>0 union all  select loc from  receiptdetail where   receiptdetail.whseid='" + dt.Rows[0]["whseid"].ToString() + "' and status is null) order by loc  ";
                    DataTable dtsku = App.GetDataTable(App.DBconWMS, strSql, "SkuLocation");// 根据料号、项目号查找设置的存储区 、查找没有被占用库位。
                    if (dtsku.Rows.Count != 0)
                    {
                        loc = dtsku.Rows[0]["loc"].ToString();
                    }

                    #endregion

                }
                return loc;
            }
            catch (Exception ex)
            {
                Log("APP.GetPutAway", ex.ToString());
                throw;

            }
        }

        #region DataTable获取
        public static DataTable GetDataTable(string connectionString, string strSQL, string name)
        {
            return Query(connectionString, strSQL, name).Tables[0];
        }

        public static DataSet Query(string connectionString, string SQLString, string name)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, name);
                }
                catch (Exception e)
                {
                    //Lic.Public.PublicMethod.WriteLog(ConfigWeb.LicErrorLog, SQLString + "; " + e.Message);
                    App.Log("App.cs-Query", e.Message.ToString());
                }
                finally
                {
                    connection.Close();
                }
                return ds;
            }
        }
        #endregion

        #region SQL语句集更新
        public static bool ExecuteSQLScriptLists(List<string> sqlLists, string connectionstring)
        {
            bool result = true;
            ExecuteSqlTran(connectionstring, sqlLists);
            if (ErrMessage.Length > 0)
            {
                //throw new Exception(DbHelperMySQL.ErrMessage);
                string temp = string.Empty;
                foreach (string sql in sqlLists)
                {
                    temp += sql + ";";
                }
                throw new Exception("Exception:" + ErrMessage + ".SQL:" + temp);
            }
            return result;
        }

        public static void ExecuteSqlTran(string connectionString, List<string> SQLStringList)
        {
            ErrMessage = string.Empty;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    foreach (string strsql in SQLStringList)
                    {
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (Exception e)
                {
                    tx.Rollback();
                    ErrMessage = e.Message.ToString();
                    App.Log("App.cs-ExecuteSqlTran", e.Message.ToString());
                }
                finally
                {
                    conn.Close();
                    
                }
            }
        }
        #endregion

        #region 存储过程执行
        /// <summary>
        /// Execute a SqlCommand that returns the first column of the first record against an existing database connection 
        /// using the provided parameters.
        /// </summary>
        /// <remarks>
        /// e.g.:  
        ///  Object obj = ExecuteScalar(connstring2, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24));
        /// </remarks>
        /// <param name="conn">an existing database connection</param>
        /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param>
        /// <param name="commandText">the stored procedure name or T-SQL command</param>
        /// <param name="commandParameters">an array of SqlParamters used to execute the command</param>
        /// <returns>An object that should be converted to the expected type using Convert.To{Type}</returns>
        public static object ExecuteScalar(string connstring2, CommandType cmdType, string cmdText, params SqlParameter[] cmdParms)
        {
            SqlCommand cmd = new SqlCommand();

            using (SqlConnection conn = new SqlConnection(connstring2))
            {
                PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
                object val = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return val;
            }
        }

        private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                cmd.Parameters.Add(parm);
            }
        }

        #endregion
    }
}
